package com.zaofan.dao;

import java.sql.SQLException;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.apache.commons.dbutils.QueryRunner;
import org.apache.commons.dbutils.handlers.BeanHandler;
import org.apache.commons.dbutils.handlers.BeanListHandler;
import org.apache.commons.dbutils.handlers.ScalarHandler;

import com.zaofan.bean.Dynamic;
import com.zaofan.bean.DynamicDTO;
import com.zaofan.constant.UrlConstant;
import com.zaofan.dao.base.DaoSupportImpl;
import com.zaofan.mobile.bean.DynamicDetail;
import com.zaofan.mobile.bean.Fans;
import com.zaofan.utils.WebUtils;

public class DynamicDao extends DaoSupportImpl<Dynamic> {

	public List<DynamicDTO> findAll(String begin, String end, String userMobile, String userName) {
		try {
			String sql = "select dynamic.*,account.mobile as userMobile,account.nickName as userName,(select group_concat(image) from userimage where userimage.dynamicId = dynamic.id) as image"
					+ " from dynamic,account " + " where dynamic.userId = account.id";
			if (!WebUtils.isEmpty(userMobile)) {
				sql += " and account.mobile = '" + userMobile + "'";
			}
			if (!WebUtils.isEmpty(userName)) {
				sql += " and account.nickName like '%" + userName + "%'";
			}
			sql += " and dynamic.topicId = 0 order by type desc ,addTime desc limit " + begin + "," + end + "";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(DynamicDTO.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Long count(String userMobile, String userName) {
		try {
			String sql = "select count(*) " + " from dynamic,account "
					+ " where dynamic.userId = account.id and dynamic.topicId = 0 ";
			if (!WebUtils.isEmpty(userMobile)) {
				sql += " and account.mobile = '" + userMobile + "'";
			}
			if (!WebUtils.isEmpty(userName)) {
				sql += " and account.nickName like '%" + userName + "%'";
			}
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new ScalarHandler<Long>());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0L;
	}

	public List<com.zaofan.mobile.bean.Dynamic> getNewDynamics(int begin, int end) {
		try {
			String sql = "select dynamic.*,account.mobile as userMobile,account.nickName as userName,concat('"
					+ UrlConstant.HOST_URL + "' ,account.photo) as userPhoto ,(select concat('" + UrlConstant.HOST_URL
					+ "' ,image) from userimage where userimage.dynamicId = dynamic.id limit 0,1"
					+ ") as image from dynamic,account where dynamic.userId = account.id and dynamic.isDelete = 0 and dynamic.topicId = 0 order by dynamic.addTime desc,type desc limit "
					+ begin + "," + end + ";";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(com.zaofan.mobile.bean.Dynamic.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<Fans> findTop4Activity() {
		try {
			String sql = "select account.id,account.nickName as userName,concat('" + UrlConstant.HOST_URL
					+ "' ,account.photo) as userPhoto,account.mobile as userMobile,count(dynamic.userId) as dynamicCount "
					+ "from dynamic,account "
					+ "where account.id= dynamic.userId and date_format(addTime,'%Y-%m')=date_format(now(),'%Y-%m') "
					+ "group by dynamic.userId " + "order by count(dynamic.userId) desc limit 0,4;";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(Fans.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<com.zaofan.mobile.bean.Dynamic> findJingxuanDynamic(String sql) {
		try {
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(com.zaofan.mobile.bean.Dynamic.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public DynamicDetail getDynamicDetail(int id) {
		try {
			String sql = "select dynamic.*,account.mobile as userMobile,account.nickName as userName,concat('"
					+ UrlConstant.HOST_URL + "',account.photo) as userPhoto ,(select group_concat(concat('"
					+ UrlConstant.HOST_URL + "',image)) as image from userimage where userimage.dynamicId = dynamic.id"
					+ ") as image from dynamic,account where dynamic.userId = account.id and dynamic.isDelete = 0 and dynamic.id = "
					+ id;
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanHandler<>(DynamicDetail.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<com.zaofan.mobile.bean.Dynamic> findDynamicByUserId(String begin, String end, String userId) {
		try {
			String sql = "select dynamic.*,account.mobile as userMobile,account.nickName as userName,concat('"
					+ UrlConstant.HOST_URL + "' ,account.photo) as userPhoto ,(select concat('" + UrlConstant.HOST_URL
					+ "' ,image) from userimage where userimage.dynamicId = dynamic.id limit 0,1"
					+ ") as image from dynamic,account where dynamic.userId = account.id and dynamic.isDelete = 0 and dynamic.topicId = 0 and dynamic.userId = "
					+ userId + " order by dynamic.addTime desc,type desc limit " + begin + "," + end + ";";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(com.zaofan.mobile.bean.Dynamic.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<com.zaofan.mobile.bean.Dynamic> findJingxuanDynamic(int begin, int end) {
		try {
			String sql = "select dynamic.*,account.mobile as userMobile,account.nickName as userName,concat('"
					+ UrlConstant.HOST_URL + "' ,account.photo) as userPhoto ,(select concat('" + UrlConstant.HOST_URL
					+ "' ,image) from userimage where userimage.dynamicId = dynamic.id limit 0,1"
					+ ") as image from dynamic,account where dynamic.userId = account.id and dynamic.isDelete = 0 and dynamic.topicId = 0 and dynamic.type = 2 order by dynamic.type desc,addTime desc limit "
					+ begin + "," + end + ";";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(com.zaofan.mobile.bean.Dynamic.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<com.zaofan.mobile.bean.Dynamic> getFollowDynamics(Integer begin, Integer end, Integer userId) {
		try {
			String sql = "select dynamic.*,account.mobile as userMobile,account.nickName as userName,concat('"
					+ UrlConstant.HOST_URL + "' ,account.photo) as userPhoto ,(select concat('" + UrlConstant.HOST_URL
					+ "' ,image) from userimage where userimage.dynamicId = dynamic.id limit 0,1"
					+ ") as image from dynamic,account where dynamic.userId = account.id and dynamic.isDelete = 0 and dynamic.topicId = 0 and dynamic.userId in (select otherId from userfollow where userId = "
					+ userId + ") order by dynamic.addTime desc,type desc limit " + begin + "," + end + ";";
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(com.zaofan.mobile.bean.Dynamic.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Dynamic findById(int id) {
		Map<String, Object> params = new HashMap<>();
		params.put("id", id + "");
		return this.findOne(params);
	}

	public com.zaofan.mobile.bean.Dynamic findDynamicWithOtherById(int replyId) {
		try {
			String sql = "select dynamic.*,account.mobile as userMobile,account.nickName as userName,concat('"
					+ UrlConstant.HOST_URL + "' ,account.photo) as userPhoto ,(select concat('" + UrlConstant.HOST_URL
					+ "' ,image) from userimage where userimage.dynamicId = dynamic.id limit 0,1"
					+ ") as image from dynamic,account where dynamic.userId = account.id and dynamic.isDelete = 0 and dynamic.id ="
					+ replyId;
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanHandler<>(com.zaofan.mobile.bean.Dynamic.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public List<DynamicDTO> findByTopicIdWithOther(String begin, String end, String topicId, String replyType,
			String type) {
		try {
			String sql = "select dynamic.*,account.mobile as userMobile,account.nickName as userName,concat('"
					+ UrlConstant.HOST_URL + "' ,account.photo) as userPhoto ,(select concat('" + UrlConstant.HOST_URL
					+ "' ,image) from userimage where userimage.dynamicId = dynamic.id limit 0,1"
					+ ") as image from dynamic,account where dynamic.userId = account.id and dynamic.isDelete = 0 and dynamic.topicId ="
					+ topicId;
			if (!WebUtils.isEmpty(replyType)) {
				sql += " and dynamic.replyType = " + replyType;
			}

			if (!WebUtils.isEmpty(type)) {
				sql += " and dynamic.type = " + type;
			}
			sql += " order by dynamic.type desc ,addTime desc limit " + begin + "," + end;
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new BeanListHandler<>(DynamicDTO.class));
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return null;
	}

	public Long countByTopicIdWithOther(String topicId, String replyType, String type) {
		try {
			String sql = "select count(*) " + " from dynamic  " + " where  dynamic.topicId =  " + topicId;
			if (!WebUtils.isEmpty(type)) {
				sql += " and dynamic.replyType = '" + replyType + "'";
			}
			if (!WebUtils.isEmpty(type)) {
				sql += " and dynamic.type = '" + type + "'";
			}
			System.err.println(sql);
			QueryRunner runner = new QueryRunner(getDataSource());
			return runner.query(sql, new ScalarHandler<Long>());
		} catch (SQLException e) {
			e.printStackTrace();
		}
		return 0L;
	}

}
